package com.farseer810.dao;

import java.util.*;
import java.sql.*;

import com.farseer810.model.Class;
import com.farseer810.utils.DatabaseHelper;

public class ClassDao {
	private ClassDao () {
	}
	
	public static void addClass(String name) throws Exception {
		Connection connection = DatabaseHelper.getConnection();
		PreparedStatement statement = connection.prepareStatement("INSERT INTO class(name) values(?)");
		statement.setString(1, name);
		
		statement.executeUpdate();
		
		statement.close();
		connection.close();
	}
	
	public static List<Class> getClassList() throws Exception {
		Connection connection = DatabaseHelper.getConnection();
		Statement statement = connection.createStatement();
		
		List<Class> result = new ArrayList<Class>();
		ResultSet resultSet = statement.executeQuery("SELECT id, name FROM class");
		while(resultSet.next()) {
			result.add(new Class(resultSet.getInt("id"), resultSet.getString("name")));
		}
		
		statement.close();
		connection.close();
		
		return result;
	}
	
	public static boolean isClassNameExist(String name) throws Exception {
		boolean isExist = true;
		Connection connection = DatabaseHelper.getConnection();
		PreparedStatement statement = connection.prepareStatement("SELECT id FROM class WHERE name=?");
		statement.setString(1, name);
		ResultSet resultSet = statement.executeQuery();
		
		resultSet.last();
		if(resultSet.getRow() == 0)
			isExist = false;
		
		resultSet.close();
		statement.close();
		connection.close();
		
		return isExist;
		
		
	}
	
	public static void createTable() throws Exception {
		Connection connection = DatabaseHelper.getConnection();
		Statement statement = connection.createStatement();
		
		ResultSet resultSet = statement.executeQuery("SHOW TABLES LIKE 'class'");
		
		
		if(!resultSet.next()) {
			String sql = "CREATE TABLE class ("
					+ "id int auto_increment primary key,"
					+ "name varchar(30)"
					+ ")ENGINE=InnoDB DEFAULT CHARSET=utf8";
			statement.executeUpdate(sql);
		}
		
		
		statement.close();
		connection.close();
		
	}
}
